const router = require('koa-router')()
//客户管理模块
router.prefix('/customer')
//1.创建客户
router.post('/', async (ctx, next) => {
    let customerName = ctx.request.body.name;
    let customerTel = ctx.request.body.tel;
    let customerCompany = ctx.request.body.company;
    let detail = ctx.request.body.detail;
    let sql = "insert into customers (customer_name, customer_tel, customer_company, customer_detail) values('" + customerName + "', '" + customerTel + "', '" + customerCompany + "', '" + detail + "')";
    await ctx.db.EXCUTE(sql, []);
    ctx.body = {
        "status": 200,
        "msg": '添加成功',
    }
})

//2.删除客户
router.delete('/', async (ctx, next) => {
    const customerId = ctx.query.customerId;
    let sql = `delete from customers where customer_id=${customerId}`
    let results = await ctx.db.EXCUTE(sql, []);
    if (results.affectedRows > 0) {
        let obj = {
            status: 200,
            msg: '删除成功'
        }
        ctx.body = JSON.stringify(obj);
    }
})

//3.修改客户
router.put('/', async (ctx, next) => {
    let id = ctx.request.body.customerId;
    let customerName = ctx.request.body.name;
    let customerTel = ctx.request.body.tel;
    let customerCompany = ctx.request.body.company;
    let detail = ctx.request.body.detail;
    let sql = `update customers set customer_name = '${customerName}', customer_tel = '${customerTel}',customer_company = '${customerCompany}',customer_detail = '${detail}' WHERE customer_id = ${id}`;
    await ctx.db.EXCUTE(sql, []);
    let obj = {
        msg: "编辑成功",
        status: 200
    }
    ctx.body = JSON.stringify(obj);
})

//4.查找客户
router.get('/', async (ctx, next) => {
    let countSql = "select count(*) from customers";
    let countResults = await ctx.db.EXCUTE(countSql, []);
    let total = countResults[0]["count(*)"];
    let pageSize = Number(ctx.request.query.size)
    let page = ctx.request.query.page
    let startRow = total > 0 ? ((page - 1) * pageSize) : 0;
    //实现分页限制查找
    let limitSql = `select * from customers order by customer_id desc limit ${startRow},${pageSize}`;
    let results = await ctx.db.EXCUTE(limitSql, []);
    let obj = {
        length: total,
        results: results,
        status: 200
    }
    ctx.body = JSON.stringify(obj);
})

//搜索客户
router.get('/searchCustomer', async (ctx, next) => {
    let name = ctx.request.query.name;
    let countSql = `select count(*) from customers where customer_detail like '%${name}%'`;
    let countResults = await ctx.db.EXCUTE(countSql, []);
    let total = countResults[0]["count(*)"];
    let pageSize = Number(ctx.request.query.size)
    let page = ctx.request.query.page
    let startRow = total > 0 ? ((page - 1) * pageSize) : 0;
    let limitSql = `select * from customers where customer_detail like '%${name}%' order by customer_id desc limit ${startRow},${pageSize}`;
    let results = await ctx.db.EXCUTE(limitSql, []);
    //完成查找的分页
    let obj = {
        status: 200,
        length: total,
        results: results
    }
    ctx.body = JSON.stringify(obj);
})
module.exports = router